Setting Field Properties

Name

The Name is a descriptive identifier for a field that can be up to 30 bytes by default (letters or numbers) including spaces. The names should be descriptive enough that anyone can easily identify them when viewing or editing records. For example, LastName, FirstName, StreetAddress, or HomePhone.

Use the Name edit box to set the field name. Note that the name of the field must be unique among all the field names in the table.

Type

After you name a field, you choose a data type for the data to be contained in the field. When you choose a field's data type, you are deciding:

The Type dropdown list defines the type of the field data.

The following table shows all the built-in general-purpose data types.
Name Description
CHAR fixed-length character strings
NCHAR fixed-length Unicode character data
VARCHAR2 variable-length character strings
VARCHAR variable-length character strings
NVARCHAR2 variable-length Unicode character data
CLOB database character set data
NCLOB Unicode national character set data
LONG variable-length character data containing up to 2 gigabytes of information
NUMBER fixed and floating-point numbers
DATE point-in-time values (dates and times)
INTERVAL DAY TO SECOND period of time in terms of days, hours, minutes, and seconds
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields
TIMESTAMP point-in-time values (dates and times) (includes fractional seconds)
TIMESTAMP WITH TIME ZONE TIMESTAMP with explicit time zone information
BLOB unstructured binary data in the database
BFILE unstructured binary data in operating-system files outside the database
RAW can be indexed and is used for data that is not to be interpreted by Oracle Database
LONG RAW cannot be indexed and is used for data that is not to be interpreted by Oracle Database
ROWID the address (rowid) of every row in the database
CHARACTER =CHAR 1
CHARACTER VARYING =VARCHAR2 1
CHAR VARYING =VARCHAR2 1
NATIONAL CHARACTER =NCHAR 1
NATIONAL CHAR =NCHAR 1
NATIONAL CHARACTER VARYING =NVARCHAR2 1
NATIONAL CHAR VARYING =NVARCHAR2 1
NCHAR VARYING =NVARCHAR2 1
NUMERIC =NUMBER 1
DECIMAL =NUMBER 1
INTEGER =NUMBER(38) 1
INT =NUMBER(38) 1
SMALLINT =NUMBER(38) 1
FLOAT =FLOAT(126) 1
DOUBLE PRECISION =FLOAT(126) 1
REAL =FLOAT(63) 1

Note: These are ANSI datatypes and datatypes from the IBM products SQL/DS and DB2. Oracle recognizes these datatypes and converts them to the equivalent Oracle datatype. Click here for detailed description on these datetypes.

Length and Scale

Use the Size edit box to define the precision (total number of digits) of the field and use Scale edit box to define the scale (number of digits to the right of the decimal point) for numeric column.

Note: Be careful when shortening the field length as it might result in data loss.

How scale factors affect numeric data storage:

Input Data Specified As Stored As
7,456,123.89 NUMBER 7456123.89
7,456,123.89 NUMBER(*,1) 7456123.9
7,456,123.89 NUMBER(9) 7456124
7,456,123.89 NUMBER(9,2) 7456123.89
7,456,123.89 NUMBER(9,1) 7456123.9
7,456,123.89 NUMBER(6) not accepted, exceeds precision
7,456,123.89 NUMBER(7,-2) 7456100

Allow Null

Allow the NULL values for the field.

Note: To set NULL as default value, see Setting Other Field Properties.

Key

A Primary Key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value.